﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using UFC.DBUtility;
using System.Collections;
using System.Configuration;

namespace SendIndustrialNetwork
{
    public class SaveContainerMESData
    {
        //public static string toAppId = ConfigurationManager.AppSettings["ToAppID_GX"].ToString();//推送数据中心目的ID（导管）
        public void Excute()
        {
            DataTable container = getContainerData();
            foreach (DataRow row in container.Rows)
            {
                string containername = row["containername"].ToString();
                string toAppId = row["toAppId"].ToString();
                DataTable containerCopy = container.Clone();
                containerCopy.Rows.Add(row.ItemArray);
                DataView containerDV = containerCopy.AsDataView();
                /*推送批次信息*/
                CommonMethods.InsertTableChangeRecord(containerDV.ToTable(true, "containerid"), "container", "containerid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(containerDV.ToTable(true, "containerid"), "containercurrentinfo", "containerid", toAppId, CommonMethods.OpType.新增);
                /*推送批次【件号】信息*/
                CommonMethods.InsertTableChangeRecord(containerDV.ToTable(true, "productid"), "product", "product", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(containerDV.ToTable(true, "productbaseid"), "productbase", "productbaseid", toAppId, CommonMethods.OpType.新增);

                /*推送批次子序号信息*/
                var sql = string.Format("SELECT c.containerid FROM container c WHERE c.parentcontainerid = '{0}' AND c.status = 1", row["containerid"].ToString());
                DataTable dtChildCon = OracleHelper.QueryTable(sql, null);
                if (dtChildCon.Rows.Count > 0)
                {
                    CommonMethods.InsertTableChangeRecord(dtChildCon, "container", "containerid", toAppId, CommonMethods.OpType.新增);
                }

                /*推送订单信息*/
                CommonMethods.InsertTableChangeRecord(containerDV.ToTable(true, "mfgorderid"), "mfgorder", "mfgorderid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(containerDV.ToTable(true, "ordertypeid"), "ordertype", "ordertypeid", toAppId, CommonMethods.OpType.新增);

                DataTable workflowDt = getWorkflowData(row["workflowid"].ToString());
                DataView workflowDV = workflowDt.AsDataView();
                /*推送工艺信息*/
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "workflowbaseid"), "workflowbase", "workflowbaseid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "workflowid"), "workflow", "workflowid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "workflowstepid"), "workflowstep", "workflowstepid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "specbaseid"), "specbase", "specbaseid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "specid"), "spec", "specid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "spectypeid"), "spectype", "spectypeid", toAppId, CommonMethods.OpType.新增);//工序类型
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "stepcheckpointid"), "stepcheckpoint", "stepcheckpointid", toAppId, CommonMethods.OpType.新增);//工序检测项（含工步）
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "objectdocinfoid"), "objectdocinfo", "objectdocinfoid", toAppId, CommonMethods.OpType.新增);//工序工艺附件

                DataTable stepinfoDt = getStepInfoData(row["workflowid"].ToString());
                if (stepinfoDt.Rows.Count > 0)
                {
                    DataView stepinfoDV = stepinfoDt.AsDataView();
                    /*推送工步信息*/
                    CommonMethods.InsertTableChangeRecord(stepinfoDV.ToTable(true, "stepinfobaseid"), "stepinfobase", "stepinfobaseid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(stepinfoDV.ToTable(true, "stepinfoid"), "stepinfo", "stepinfoid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(stepinfoDV.ToTable(true, "spectypeid"), "spectype", "spectypeid", toAppId, CommonMethods.OpType.新增);//工步类型
                }
                /*-----------------------------------------------------
                 *-----------推送工序、工步下的工装、辅材信息-------------------
                 *------------------------------------------------------*/
                string stepInfoIDs = string.Empty; //工步ID集
                foreach (DataRow item in stepinfoDt.AsDataView().ToTable(true, "stepinfoid").Rows)
                {
                    stepInfoIDs += item["stepinfoid"].ToString() + ",";
                }
                stepInfoIDs = stepInfoIDs.TrimEnd(',');
                string workflowStepIDs = string.Empty; //工序ID集
                foreach (DataRow item in workflowDV.ToTable(true, "workflowstepid").Rows)
                {
                    workflowStepIDs += item["workflowStepID"].ToString() + ",";
                }
                workflowStepIDs = workflowStepIDs.TrimEnd(',');
                /*工步下工装、辅材信息*/
                getWorkflowStepTools(stepInfoIDs, "stepinfotoolsid", toAppId);
                /*工序下工装、辅材信息*/
                getWorkflowStepTools(workflowStepIDs, "workflowsteptoolsid", toAppId);

                /*-----------------------------------------------------
                *-----------推送bom信息-------------------
                *------------------------------------------------------*/
                DataTable bomDT = getBomData(row["bomid"].ToString());
                if (bomDT.Rows.Count > 0)
                {
                    DataView bomDV = bomDT.AsDataView();
                    CommonMethods.InsertTableChangeRecord(bomDV.ToTable(true, "productmateriallistitemid"), "productmateriallistitem", "productmateriallistitemid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(bomDV.ToTable(true, "productid"), "product", "productid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(bomDV.ToTable(true, "productbaseid"), "productbase", "productbaseid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(bomDV.ToTable(true, "bomid"), "bom", "bomid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(bomDV.ToTable(true, "bombaseid"), "bombase", "bombaseid", toAppId, CommonMethods.OpType.新增);
                }

            }
        }
        /// <summary>
        /// 获取需要下发到产线的件号 --对应--流水卡信息
        /// </summary>
        /// <returns></returns>
        private DataTable getContainerData()
        {
            string sql = @"select distinct cci.containerid,cci.containername,decode(cci.isinstore,-1,c.mfgworkflowid,cci.workflowid) workflowid,
                                  pb.productname,p.description,c4.customobjectitemsdesc toappid,cci.bomid,cci.mfgorderid,m.ordertypeid,cci.productid,cci.productbaseid
                            from containercurrentinfo cci
                            left join container c on c.containerid = cci.containerid
                            left join product p on p.productid = cci.productid
                            left join productbase pb on pb.productbaseid = p.productbaseid
                            left join mfgorder m on m.mfgorderid =cci.mfgorderid
                            inner join customobjectitems c2 on c2.customobjectitemsname =pb.productname and c2.factoryid =cci.factoryid
                            left join customobject c3 on c3.customobjectid=c2.customobjectid
                            left join customobjectitems c4 on c4.customobjectid =c3.customobjectid and c4.customobjectitemsname='toappid'--不为空
                            left join customobjectitems c5 on c5.customobjectid =c3.customobjectid and c5.customobjectitemsname='起草人'--不为空
                            left join customobjectitems c6 on c6.customobjectid =c3.customobjectid and c6.customobjectitemsname='审批人'--不为空
                            left join customobjectitems c7 on c7.customobjectid =c3.customobjectid and c7.customobjectitemsname='签发人'--不为空
                            where cci.isinstore != 1 and c3.customobjectname like 'CX-%' --and cci.containerid='0004108000016122'
                            and cci.originalstartdate>=to_date('2024-09-27','yyyy-mm-dd') and cci.techniqueemployeeid is not null ---流水卡校对人不为空（工艺确认）
                            and c4.customobjectitemsdesc is not null 
                            and c5.customobjectitemsdesc is not null 
                            and c6.customobjectitemsdesc is not null 
                            and c7.customobjectitemsdesc is not null 
                            and not exists (
                        	    select 1 from sync_tablechangerecord st 
                        	    left join sync_sendtoappid ss on ss.parentid=st.id 
                        	    where ss.toappid=c4.customobjectitemsdesc and st.uniquecolvalue =cci.containerid and st.tablename =upper('containercurrentinfo') and st.operatetype ='1'
                        	)";
            return OracleHelper.QueryTable(sql, null);
        }
        /// <summary>
        /// 查询工艺相关信息
        /// </summary>
        /// <param name="workflowid"></param>
        /// <returns></returns>
        private DataTable getWorkflowData(string workflowid)
        {
            string sql = @"select ws.workflowstepid,w.workflowid,w.workflowbaseid,ws.specbaseid,sc.specid,s.stepcheckpointid,sc.spectypeid,o.objectdocinfoid 
                        from workflow w 
                        left join workflowstep ws  on ws.workflowid = w.workflowid 
                        left join specbase sb on sb.specbaseid = ws.specbaseid
                        left join spec sc on sc.specid = nvl(sb.revofrcdid,ws.specid)
                        left join stepcheckpoint s on s.workflowstepid =ws.workflowstepid
                        left join objectdocinfo o on (o.parentid =sc.specid or o.parentid=w.workflowid) 
                        where ws.workflowid ='{0}'";
            return OracleHelper.QueryTable(string.Format(sql, workflowid), null);
        }

        /// <summary>
        /// 根据工艺id,查询工序下相关的工步信息
        /// </summary>
        /// <param name="workflowstepid"></param>
        /// <returns></returns>
        private DataTable getStepInfoData(string workflowid)
        {
            string sql = @"select s.stepinfoid,sib.stepinfobaseid,s.spectypeid 
                        from  workflowstep ws
                        left join stepinfo s on s.workflowstepid=ws.workflowstepid
                        inner join stepinfobase sib on sib.revofrcdid = s.stepinfoid --取最新版
                        where ws.workflowid ='{0}'";
            return OracleHelper.QueryTable(string.Format(sql, workflowid), null);
        }

        /// <summary>
        /// 根据工序、工步ids,查询相关的工装、辅材信息
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="workflowstepids"></param>
        /// <returns></returns>
        private void getWorkflowStepTools(string ids, string idtype, string toAppId)
        {
            try
            {
                string sql = string.Format(@"SELECT sit.{0},mt.measuretoolid,st.specialtoolid,ct.cuttoolid,ax.auxmaterialid
                        FROM {1} sit 
                        LEFT JOIN measuretool mt ON mt.measuretoolid = sit.tools
                        LEFT JOIN specialtool st ON st.specialtoolid = sit.tools
                        LEFT JOIN cuttool ct ON ct.cuttoolid = sit.tools
                        LEFT JOIN auxmaterial ax ON ax.auxmaterialid = sit.tools
                        WHERE sit.{2} IN ('{3}')", idtype, idtype.Equals("stepinfotoolsid") ? "stepinfotools" : "workflowsteptools", idtype.Equals("stepinfotoolsid") ? "stepinfoid" : "workflowstepid", ids.Replace(",", "','"));
                DataTable dtTool = OracleHelper.QueryTable(sql, null);
                if (dtTool.Rows.Count > 0)
                {
                    DataView dvTool = dtTool.AsDataView();
                    /*推送工序、工步的工具辅材关系表信息*/
                    if (idtype.Equals("stepinfotoolsid"))
                    {
                        CommonMethods.InsertTableChangeRecord(dvTool.ToTable(true, "stepinfotoolsid"), "stepinfotools", "stepinfotoolsid", toAppId, CommonMethods.OpType.新增);
                    }
                    else
                    {
                        CommonMethods.InsertTableChangeRecord(dvTool.ToTable(true, "workflowsteptoolsid"), "workflowsteptools", "workflowsteptoolsid", toAppId, CommonMethods.OpType.新增);
                    }
                    /*推送工序、工步的量具信息*/
                    CommonMethods.InsertTableChangeRecord(dvTool.ToTable(true, "measuretoolid"), "measuretool", "measuretoolid", toAppId, CommonMethods.OpType.新增);
                    /*推送工序、工步的夹模具信息*/
                    CommonMethods.InsertTableChangeRecord(dvTool.ToTable(true, "specialtoolid"), "specialtool", "specialtoolid", toAppId, CommonMethods.OpType.新增);
                    /*推送工序、工步的刀具信息*/
                    CommonMethods.InsertTableChangeRecord(dvTool.ToTable(true, "cuttoolid"), "cuttool", "cuttoolid", toAppId, CommonMethods.OpType.新增);
                    /*推送工序、工步的辅材信息*/
                    CommonMethods.InsertTableChangeRecord(dvTool.ToTable(true, "auxmaterialid"), "auxmaterial", "auxmaterialid", toAppId, CommonMethods.OpType.新增);
                }
            }
            catch (Exception ex)
            {

            }

        }

        /// <summary>
        /// 根据bomid查询相关表数据
        /// </summary>
        /// <param name="bomid"></param>
        /// <returns></returns>
        private DataTable getBomData(string bomid)
        {
            var sql = @"select pm.productmateriallistitemid,p.productid,pb.productbaseid,b.bomid,bb.bombaseid 
                    from productmateriallistitem pm
                    left join productbase pb1 on pb1.productbaseid=pm.productbaseid  
                    left join product p on p.productid=nvl(pb1.revofrcdid,pm.productid) 
                    left join productbase pb on pb.productbaseid=p.productbaseid 
                    left join bom b on b.bomid = pm.bomid
                    left join bombase bb on bb.bombaseid = b.bombaseid 
                    where pm.bomid='{0}'";
            return OracleHelper.QueryTable(string.Format(sql, bomid), null);
        }
    }
}
